import mysql.connector
import pandas as pd
import matplotlib.pyplot as plt
import matplotlib.dates as mdates
import seaborn as sb
import numpy as np
from enum import Enum
cnx = mysql.connector.connect(user='user', password='root',
host='127.0.0.1',
database='data_analytics')
cursor = cnx.cursor(dictionary = True)
The primary goal of this project is to provide a comprehensive exploration of the data, enabling us to identify patterns, relationships, and key factors crucial for making informed investment decisions. By delving deep into the historical trends and financial metrics, we aim to not only maximize returns but also effectively manage risk within the stock market landscape.
The core objective of this project is to perform Exploratory Data Analysis (EDA) to gain a profound understanding of the available dataset. Through this analysis, we intend to uncover significant insights that could potentially guide strategic investment decisions.
Our approach involves meticulous examination and visualization of the past 10 years of historical stock price data alongside various financial indicators. By employing statistical analysis, visualization techniques, and correlation studies, we seek to unveil hidden patterns and relationships among different variables.
In this section, we will first focus on analyzing the Stock Price data. The aim is to unveil an overarching trend within the market and discern the nuances of price action. This analysis serves as a foundational exploration, offering insights into the broader movements and behaviors within the stock market.
sql_query = '''
SELECT DISTINCT
StockPrices.date,
StockPrices.stock_id,
StockPrices.open,
StockPrices.close,
StockPrices.high,
StockPrices.low,
StockPrices.volume,
StocksIndustries.industry_id
FROM
StocksIndustries
INNER JOIN StockPrices USING(stock_id)
'''
cursor.execute(sql_query)
result = cursor.fetchall()
df = pd.DataFrame(result)
df = df[::-1].reset_index(drop=True)
df['return'] = df.groupby(['stock_id', 'industry_id'])['close'].pct_change()
df['cum_returns'] = df.groupby(['stock_id', 'industry_id'])['return'].transform(lambda x: (1 + x).cumprod())
df
| date | stock_id | open | close | high | low | volume | industry_id | return | cum_returns | |
|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2013-11-06 | WELL | 63.00 | 63.27 | 63.560 | 62.8500 | 1411071 | Utilities | NaN | NaN |
| 1 | 2013-11-07 | WELL | 62.71 | 61.43 | 62.830 | 61.3700 | 1526318 | Utilities | -0.029082 | 0.970918 |
| 2 | 2013-11-08 | WELL | 61.00 | 60.41 | 61.000 | 59.5000 | 2373307 | Utilities | -0.016604 | 0.954797 |
| 3 | 2013-11-11 | WELL | 60.41 | 60.08 | 60.840 | 59.9200 | 1692912 | Utilities | -0.005463 | 0.949581 |
| 4 | 2013-11-12 | WELL | 60.00 | 59.64 | 60.190 | 59.3400 | 1683871 | Utilities | -0.007324 | 0.942627 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 138375 | 2023-10-30 | CMCSA | 39.96 | 40.45 | 40.620 | 39.9400 | 18202260 | Communication Services | 0.020434 | 1.692115 |
| 138376 | 2023-10-31 | CMCSA | 40.70 | 41.29 | 41.340 | 40.6688 | 18860960 | Communication Services | 0.020766 | 1.727254 |
| 138377 | 2023-11-01 | CMCSA | 41.37 | 41.82 | 42.135 | 41.3400 | 19505410 | Communication Services | 0.012836 | 1.749425 |
| 138378 | 2023-11-02 | CMCSA | 41.86 | 42.50 | 42.580 | 41.8600 | 16755390 | Communication Services | 0.016260 | 1.777871 |
| 138379 | 2023-11-03 | CMCSA | 42.86 | 43.18 | 43.420 | 42.8201 | 19005630 | Communication Services | 0.016000 | 1.806317 |
138380 rows × 10 columns
groups = df.groupby('industry_id')
industry_ids = df['industry_id'].dropna().unique()
fig, axes = plt.subplots(nrows=len(industry_ids), ncols=1, figsize=(10, 5*len(industry_ids)), sharex=True)
# Iterate over each industry_id and plot close values
for i, industry_id in enumerate(industry_ids):
ax = axes[i]
industry_data = groups.get_group(industry_id)
# Iterate over each stock in the industry and plot the close values
for stock_id, stock_data in industry_data.groupby('stock_id'):
ax.plot(stock_data['date'], stock_data['close'], label=stock_id)
ax.set_title(industry_id)
ax.set_xlabel('Date')
ax.set_ylabel('Close Price')
ax.legend()
# Plot Correlation Coefficient of Closing Price
fig, axes = plt.subplots(nrows=len(industry_ids), ncols=1, figsize=(10, 5*len(industry_ids)), sharex=True)
for i, industry_id in enumerate(industry_ids):
ax = axes[i]
ax.set_title(industry_id)
industry_data = groups.get_group(industry_id)
# Pivot the DataFrame to have stock_id as columns and closing prices as values
close_df = industry_data.pivot(index='date', columns='stock_id', values='close')
pearsoncorr = close_df.corr(method ='pearson')
sb.heatmap(pearsoncorr, ax = ax, xticklabels=pearsoncorr.columns, yticklabels=pearsoncorr.columns, cmap='RdBu_r', annot=True, linewidth=0.5)
fig, axes = plt.subplots(nrows=len(industry_ids), ncols=1, figsize=(10, 5*len(industry_ids)), sharex=True)
# Iterate over each industry_id and plot cumulative returns values
for i, industry_id in enumerate(industry_ids):
ax = axes[i]
industry_data = groups.get_group(industry_id)
# Iterate over each stock in the industry and plot the cumulative returns values
for stock_id, stock_data in industry_data.groupby('stock_id'):
ax.plot(stock_data['date'], stock_data['cum_returns'], label=stock_id)
ax.set_title(industry_id)
ax.set_xlabel('Date')
ax.set_ylabel('Cumulative Returns Price')
ax.legend()
fig, axes = plt.subplots(nrows=len(industry_ids), ncols=1, figsize=(10, 5*len(industry_ids)), sharex=True)
for i, industry_id in enumerate(industry_ids):
ax = axes[i]
ax.set_title(industry_id)
industry_data = groups.get_group(industry_id)
close_df = industry_data.pivot(index='date', columns='stock_id', values='cum_returns')
pearsoncorr = close_df.corr(method ='pearson')
sb.heatmap(pearsoncorr, ax = ax, xticklabels=pearsoncorr.columns, yticklabels=pearsoncorr.columns, cmap='RdBu_r', annot=True, linewidth=0.5)
Upon delving into the historical stock prices, our analysis pinpointed outliers within the specific industries. While the majority of stocks within these industries exhibit a tendency to correlate, notable exceptions emerged.
Particularly, within the Industrials, Financials, and Energy sectors, the closing prices and cumulative returns displayed a contrary trend for some stocks.
Conversely, the Information Technology sector displayed a consistent directional movement, yet the cumulative returns graph highlighted NVDA stock's exponential growth trajectory.
In this section, our concentration will be on dissecting NVDA stock's exponential growth trajectory through an examination of its financial metrics. AAPL, on the other hand, will be utilized as the benchmark stock for comparative analysis. By juxtaposing the financial indicators of these two stocks, we aim to dissect and comprehend the factors contributing to NVDA's exceptional growth, utilizing AAPL as a reference point for a comprehensive comparative assessment.
sql_query = '''
SELECT DISTINCT
*
FROM
StockFinancialData
WHERE stock_id = 'AAPL' OR stock_id = 'NVDA';
'''
cursor.execute(sql_query)
result = cursor.fetchall()
df = pd.DataFrame(result)
Context: Due to differing year-ending financial statements between AAPL and NVDA—AAPL's year-end being September 2023 and NVDA's being January 2023—adjustments are necessary in aggregating the years. Specifically, the aggregation aims to exclude AAPL's year 0 and NVDA's year 10 from the analysis, aligning the data effectively to accommodate these dissimilar year-end reporting periods.
df.loc[df['stock_id'] == 'AAPL', 'year'] -= 1
df = df[df['year'] != -1]
df = df[df['year'] != 9]
df
| id | namespace | fact | value | units | start_date | end_date | stock_id | year | |
|---|---|---|---|---|---|---|---|---|---|
| 19 | 20 | us-gaap | AccountsPayableCurrent | 64115000000.0000 | USD | 2022-09-24 00:00:00 | 2022-09-24 00:00:00 | AAPL | 0 |
| 20 | 21 | us-gaap | AccountsReceivableNetCurrent | 28184000000.0000 | USD | 2022-09-24 00:00:00 | 2022-09-24 00:00:00 | AAPL | 0 |
| 21 | 22 | us-gaap | AssetsCurrent | 135405000000.0000 | USD | 2022-09-24 00:00:00 | 2022-09-24 00:00:00 | AAPL | 0 |
| 22 | 23 | us-gaap | AssetsNoncurrent | 217350000000.0000 | USD | 2022-09-24 00:00:00 | 2022-09-24 00:00:00 | AAPL | 0 |
| 23 | 24 | us-gaap | CashAndCashEquivalentsAtCarryingValue | 23646000000.0000 | USD | 2022-09-24 00:00:00 | 2022-09-24 00:00:00 | AAPL | 0 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 350 | 768 | us-gaap | CostOfGoodsAndServicesSold | 2082030000.0000 | USD | 2015-01-29 00:00:00 | 2015-01-29 00:00:00 | NVDA | 8 |
| 351 | 769 | us-gaap | OtherLiabilitiesCurrent | 602807000.0000 | USD | 2015-01-29 00:00:00 | 2015-01-29 00:00:00 | NVDA | 8 |
| 352 | 770 | us-gaap | OtherAssetsCurrent | 70174000.0000 | USD | 2015-01-29 00:00:00 | 2015-01-29 00:00:00 | NVDA | 8 |
| 353 | 771 | us-gaap | LongTermDebtCurrent | 0.0000 | USD | 2015-01-29 00:00:00 | 2015-01-29 00:00:00 | NVDA | 8 |
| 354 | 772 | us-gaap | LongTermDebtNoncurrent | 1384342000.0000 | USD | 2015-01-29 00:00:00 | 2015-01-29 00:00:00 | NVDA | 8 |
336 rows × 9 columns
convert_index_to_years = {
0: 2022,
1: 2021,
2: 2020,
3: 2019,
4: 2018,
5: 2017,
6: 2016,
7: 2015,
8: 2014
}
df['year'] = df['year'].replace(convert_index_to_years)
df
| id | namespace | fact | value | units | start_date | end_date | stock_id | year | |
|---|---|---|---|---|---|---|---|---|---|
| 19 | 20 | us-gaap | AccountsPayableCurrent | 64115000000.0000 | USD | 2022-09-24 00:00:00 | 2022-09-24 00:00:00 | AAPL | 2022 |
| 20 | 21 | us-gaap | AccountsReceivableNetCurrent | 28184000000.0000 | USD | 2022-09-24 00:00:00 | 2022-09-24 00:00:00 | AAPL | 2022 |
| 21 | 22 | us-gaap | AssetsCurrent | 135405000000.0000 | USD | 2022-09-24 00:00:00 | 2022-09-24 00:00:00 | AAPL | 2022 |
| 22 | 23 | us-gaap | AssetsNoncurrent | 217350000000.0000 | USD | 2022-09-24 00:00:00 | 2022-09-24 00:00:00 | AAPL | 2022 |
| 23 | 24 | us-gaap | CashAndCashEquivalentsAtCarryingValue | 23646000000.0000 | USD | 2022-09-24 00:00:00 | 2022-09-24 00:00:00 | AAPL | 2022 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 350 | 768 | us-gaap | CostOfGoodsAndServicesSold | 2082030000.0000 | USD | 2015-01-29 00:00:00 | 2015-01-29 00:00:00 | NVDA | 2014 |
| 351 | 769 | us-gaap | OtherLiabilitiesCurrent | 602807000.0000 | USD | 2015-01-29 00:00:00 | 2015-01-29 00:00:00 | NVDA | 2014 |
| 352 | 770 | us-gaap | OtherAssetsCurrent | 70174000.0000 | USD | 2015-01-29 00:00:00 | 2015-01-29 00:00:00 | NVDA | 2014 |
| 353 | 771 | us-gaap | LongTermDebtCurrent | 0.0000 | USD | 2015-01-29 00:00:00 | 2015-01-29 00:00:00 | NVDA | 2014 |
| 354 | 772 | us-gaap | LongTermDebtNoncurrent | 1384342000.0000 | USD | 2015-01-29 00:00:00 | 2015-01-29 00:00:00 | NVDA | 2014 |
336 rows × 9 columns
class Facts(Enum):
TOTAL_NET_SALES = 'RevenueFromContractWithCustomerExcludingAssessedTax'
GROSS_PROFIT = 'GrossProfit'
OPERATING_PROFIT_LOSS = 'OperatingIncomeLoss'
EARNINGS_PER_SHARE_DILUTED = 'EarningsPerShareDiluted'
CURRENT_ASSETS = 'AssetsCurrent'
NON_CURRENT_ASSETS = 'AssetsNoncurrent'
CURRENT_LIABILITIES = 'LiabilitiesCurrent'
NON_CURRENT_LIABILITES = 'LiabilitiesNoncurrent'
DIVIDENDS = 'CommonStockDividendsPerShareDeclared'
CASH_GENERATED_BY_OPERATING_ACTIVITIES = 'NetCashProvidedByUsedInOperatingActivities'
COST_OF_SALES = 'CostOfGoodsAndServicesSold'
CURRENT_ASSETS_INVENTORIES = 'InventoryNet'
CURRENT_ASSETS_DEBTORS = 'AccountsReceivableNetCurrent'
CURRENT_ASSETS_CASH = 'CashAndCashEquivalentsAtCarryingValue'
CURRENT_ASSETS_OTHERS = 'OtherAssetsCurrent'
CURRENT_LIABILTIES_LOANS = 'LongTermDebtCurrent'
CURRENT_LIABILITIES_CREDITORS = 'AccountsPayableCurrent'
CURRENT_LIABILITIES_OTHERS = 'OtherLiabilitiesCurrent'
NON_CURRENT_LOANS = 'LongTermDebtNoncurrent'
data = pd.pivot_table(df, values = 'value', index = ['fact', 'stock_id'], columns = ['year'])
data
| year | 2014 | 2015 | 2016 | 2017 | 2018 | 2019 | 2020 | 2021 | 2022 | |
|---|---|---|---|---|---|---|---|---|---|---|
| fact | stock_id | |||||||||
| AccountsPayableCurrent | AAPL | 30196000000.0 | 35490000000.0 | 37294000000.0 | 49049000000.0 | 55888000000.0 | 46236000000.0 | 42296000000.0 | 54763000000.0 | 64115000000.0 |
| NVDA | 293223000.0 | 296000000.0 | 485000000.0 | 596000000.0 | 511000000.0 | 687000000.0 | 1201000000.0 | 1783000000.0 | 1193000000.0 | |
| AccountsReceivableNetCurrent | AAPL | 17460000000.0 | 16849000000.0 | 15754000000.0 | 17874000000.0 | 23186000000.0 | 22926000000.0 | 16120000000.0 | 26278000000.0 | 28184000000.0 |
| NVDA | 473637000.0 | 505000000.0 | 826000000.0 | 1265000000.0 | 1424000000.0 | 1657000000.0 | 2429000000.0 | 4650000000.0 | 3827000000.0 | |
| AssetsCurrent | AAPL | 68531000000.0 | 89378000000.0 | 106869000000.0 | 128645000000.0 | 131339000000.0 | 162819000000.0 | 143713000000.0 | 134836000000.0 | 135405000000.0 |
| NVDA | 5713297000.0 | 6053000000.0 | 8536000000.0 | 9255000000.0 | 10557000000.0 | 13690000000.0 | 16055000000.0 | 28829000000.0 | 23073000000.0 | |
| AssetsNoncurrent | AAPL | 163308000000.0 | 201101000000.0 | 214817000000.0 | 246674000000.0 | 234386000000.0 | 175697000000.0 | 180175000000.0 | 216166000000.0 | 217350000000.0 |
| NVDA | 1488000000.0 | 1317000000.0 | 1305000000.0 | 1986000000.0 | 2735000000.0 | 3625000000.0 | 12736000000.0 | 15358000000.0 | 18109000000.0 | |
| CashAndCashEquivalentsAtCarryingValue | AAPL | 13844000000.0 | 21120000000.0 | 20484000000.0 | 20289000000.0 | 25913000000.0 | 48844000000.0 | 38016000000.0 | 34940000000.0 | 23646000000.0 |
| NVDA | 496654000.0 | 596000000.0 | 1766000000.0 | 4002000000.0 | 782000000.0 | 10896000000.0 | 847000000.0 | 1990000000.0 | 3389000000.0 | |
| CommonStockDividendsPerShareDeclared | AAPL | 1.82 | 1.98 | 2.18 | 2.4 | 2.72 | 3.0 | 0.795 | 0.85 | 0.9 |
| NVDA | 0.34 | 0.115 | NaN | NaN | NaN | NaN | NaN | NaN | 0.16 | |
| CostOfGoodsAndServicesSold | AAPL | 112258000000.0 | 140089000000.0 | 131376000000.0 | 141048000000.0 | 163756000000.0 | 161782000000.0 | 169559000000.0 | 212981000000.0 | 223546000000.0 |
| NVDA | 2082030000.0 | 2199000000.0 | 2847000000.0 | 3892000000.0 | 4545000000.0 | 4150000000.0 | 6279000000.0 | 9439000000.0 | 11618000000.0 | |
| EarningsPerShareDiluted | AAPL | 6.45 | 9.22 | 8.31 | 9.21 | 11.91 | 11.89 | 3.28 | 5.61 | 6.11 |
| NVDA | 1.12 | 1.08 | 2.57 | 4.82 | 6.63 | 4.52 | 6.9 | 3.85 | 1.74 | |
| GrossProfit | AAPL | 70537000000.0 | 93626000000.0 | 84263000000.0 | 88186000000.0 | 101839000000.0 | 98392000000.0 | 104956000000.0 | 152836000000.0 | 170782000000.0 |
| NVDA | 2599477000.0 | 2811000000.0 | 4063000000.0 | 5822000000.0 | 7171000000.0 | 6768000000.0 | 10396000000.0 | 17475000000.0 | 15356000000.0 | |
| InventoryNet | AAPL | 2111000000.0 | 2349000000.0 | 2132000000.0 | 4855000000.0 | 3956000000.0 | 4106000000.0 | 4061000000.0 | 6580000000.0 | 4946000000.0 |
| NVDA | 482893000.0 | 418000000.0 | 794000000.0 | 796000000.0 | 1575000000.0 | 979000000.0 | 1826000000.0 | 2605000000.0 | 5159000000.0 | |
| LiabilitiesCurrent | AAPL | 63448000000.0 | 80610000000.0 | 79006000000.0 | 100814000000.0 | 116866000000.0 | 105718000000.0 | 105392000000.0 | 125481000000.0 | 153982000000.0 |
| NVDA | 896030000.0 | 2351000000.0 | 1788000000.0 | 1153000000.0 | 1329000000.0 | 1784000000.0 | 3925000000.0 | 4335000000.0 | 6563000000.0 | |
| LiabilitiesNoncurrent | AAPL | 56844000000.0 | 90514000000.0 | 114431000000.0 | 140458000000.0 | 141712000000.0 | 142310000000.0 | 153157000000.0 | 162431000000.0 | 148101000000.0 |
| NVDA | 1887000000.0 | 463000000.0 | 2260000000.0 | 2617000000.0 | 2621000000.0 | 3327000000.0 | 7973000000.0 | 13240000000.0 | 12518000000.0 | |
| LongTermDebtCurrent | AAPL | 0.0 | 2500000000.0 | 3500000000.0 | 6496000000.0 | 8784000000.0 | 10260000000.0 | 8773000000.0 | 9613000000.0 | 11128000000.0 |
| NVDA | 0.0 | 1413000000.0 | 796000000.0 | 15000000.0 | 0.0 | 0.0 | 999000000.0 | 0.0 | 1250000000.0 | |
| LongTermDebtNoncurrent | AAPL | 28987000000.0 | 53463000000.0 | 75427000000.0 | 97207000000.0 | 93735000000.0 | 91807000000.0 | 98667000000.0 | 109106000000.0 | 98959000000.0 |
| NVDA | 1384342000.0 | 0.0 | 1983000000.0 | 1985000000.0 | 1988000000.0 | 5964000000.0 | 5964000000.0 | 10946000000.0 | 9703000000.0 | |
| NetCashProvidedByUsedInOperatingActivities | AAPL | 59713000000.0 | 81266000000.0 | 65824000000.0 | 63598000000.0 | 77434000000.0 | 69391000000.0 | 80674000000.0 | 104038000000.0 | 122151000000.0 |
| NVDA | 905656000.0 | 1175000000.0 | 1672000000.0 | 3502000000.0 | 3743000000.0 | 4761000000.0 | 5822000000.0 | 9108000000.0 | 5641000000.0 | |
| OperatingIncomeLoss | AAPL | 52503000000.0 | 71230000000.0 | 60024000000.0 | 61344000000.0 | 70898000000.0 | 63930000000.0 | 66288000000.0 | 108949000000.0 | 119437000000.0 |
| NVDA | 758989000.0 | 747000000.0 | 1934000000.0 | 3210000000.0 | 3804000000.0 | 2846000000.0 | 4532000000.0 | 10041000000.0 | 4224000000.0 | |
| OtherAssetsCurrent | AAPL | 9806000000.0 | 9539000000.0 | 8283000000.0 | 13936000000.0 | 12087000000.0 | 12352000000.0 | 11264000000.0 | 14111000000.0 | 21223000000.0 |
| NVDA | 70174000.0 | 93000000.0 | 118000000.0 | 86000000.0 | 136000000.0 | 157000000.0 | 239000000.0 | 366000000.0 | 791000000.0 | |
| OtherLiabilitiesCurrent | AAPL | 0.0 | 0.0 | 0.0 | 0.0 | 32687000000.0 | 37720000000.0 | 42684000000.0 | 47493000000.0 | 60845000000.0 |
| NVDA | 602807000.0 | 642000000.0 | 507000000.0 | 542000000.0 | 818000000.0 | 1097000000.0 | 1725000000.0 | 2552000000.0 | 4120000000.0 | |
| RevenueFromContractWithCustomerExcludingAssessedTax | AAPL | 182795000000.0 | 233715000000.0 | 215639000000.0 | 229234000000.0 | 265595000000.0 | 260174000000.0 | 274515000000.0 | 365817000000.0 | 394328000000.0 |
| NVDA | 4682000000.0 | 5010000000.0 | 6910000000.0 | 9714000000.0 | 11716000000.0 | 10918000000.0 | 16675000000.0 | 26914000000.0 | 26974000000.0 |
def aggregate_values(fact, stock_id):
return data.loc[fact, stock_id] / 1000000
rename_indexes = {
Facts.TOTAL_NET_SALES.value : 'Total Net Sales',
Facts.GROSS_PROFIT.value : 'Gross Profit',
Facts.OPERATING_PROFIT_LOSS.value : 'Operating Profit',
Facts.EARNINGS_PER_SHARE_DILUTED.value : 'Earnings Per Share',
Facts.CURRENT_ASSETS.value : 'Current Assets',
Facts.NON_CURRENT_ASSETS.value : 'Non-current Assets',
Facts.CURRENT_LIABILITIES.value : 'Current Liabilities',
Facts.NON_CURRENT_LIABILITES.value : 'Non-current Liabilities',
Facts.DIVIDENDS.value : 'Dividends' ,
Facts.CASH_GENERATED_BY_OPERATING_ACTIVITIES.value : 'Net Cash Flow from Operating Activities',
Facts.COST_OF_SALES.value : 'Cost of Sales',
Facts.CURRENT_ASSETS_INVENTORIES.value : 'Inventories',
Facts.CURRENT_ASSETS_DEBTORS.value : 'Trade Debtors',
Facts.CURRENT_ASSETS_CASH.value : 'Cash and Cash Equivalents',
Facts.CURRENT_ASSETS_OTHERS.value : 'Other Current Assets',
Facts.CURRENT_LIABILTIES_LOANS.value : 'Current Long Term Debt',
Facts.CURRENT_LIABILITIES_CREDITORS.value : 'Trade Creditors',
Facts.CURRENT_LIABILITIES_OTHERS.value : 'Other Current Liabilities',
Facts.NON_CURRENT_LOANS.value : 'Non-current Long Term Debt'
}
stock_ids = ['AAPL', 'NVDA']
data = data.rename(index = rename_indexes)
for stock_id in stock_ids:
for fact in list(Facts):
if fact.value == Facts.DIVIDENDS.value or fact.value == Facts.EARNINGS_PER_SHARE_DILUTED.value:
continue
data.loc[rename_indexes[fact.value], stock_id]= data.loc[rename_indexes[fact.value], stock_id] / 1000000
data
| year | 2014 | 2015 | 2016 | 2017 | 2018 | 2019 | 2020 | 2021 | 2022 | |
|---|---|---|---|---|---|---|---|---|---|---|
| fact | stock_id | |||||||||
| Trade Creditors | AAPL | 30196.0 | 35490.0 | 37294.0 | 49049.0 | 55888.0 | 46236.0 | 42296.0 | 54763.0 | 64115.0 |
| NVDA | 293.223 | 296.0 | 485.0 | 596.0 | 511.0 | 687.0 | 1201.0 | 1783.0 | 1193.0 | |
| Trade Debtors | AAPL | 17460.0 | 16849.0 | 15754.0 | 17874.0 | 23186.0 | 22926.0 | 16120.0 | 26278.0 | 28184.0 |
| NVDA | 473.637 | 505.0 | 826.0 | 1265.0 | 1424.0 | 1657.0 | 2429.0 | 4650.0 | 3827.0 | |
| Current Assets | AAPL | 68531.0 | 89378.0 | 106869.0 | 128645.0 | 131339.0 | 162819.0 | 143713.0 | 134836.0 | 135405.0 |
| NVDA | 5713.297 | 6053.0 | 8536.0 | 9255.0 | 10557.0 | 13690.0 | 16055.0 | 28829.0 | 23073.0 | |
| Non-current Assets | AAPL | 163308.0 | 201101.0 | 214817.0 | 246674.0 | 234386.0 | 175697.0 | 180175.0 | 216166.0 | 217350.0 |
| NVDA | 1488.0 | 1317.0 | 1305.0 | 1986.0 | 2735.0 | 3625.0 | 12736.0 | 15358.0 | 18109.0 | |
| Cash and Cash Equivalents | AAPL | 13844.0 | 21120.0 | 20484.0 | 20289.0 | 25913.0 | 48844.0 | 38016.0 | 34940.0 | 23646.0 |
| NVDA | 496.654 | 596.0 | 1766.0 | 4002.0 | 782.0 | 10896.0 | 847.0 | 1990.0 | 3389.0 | |
| Dividends | AAPL | 1.82 | 1.98 | 2.18 | 2.4 | 2.72 | 3.0 | 0.795 | 0.85 | 0.9 |
| NVDA | 0.34 | 0.115 | NaN | NaN | NaN | NaN | NaN | NaN | 0.16 | |
| Cost of Sales | AAPL | 112258.0 | 140089.0 | 131376.0 | 141048.0 | 163756.0 | 161782.0 | 169559.0 | 212981.0 | 223546.0 |
| NVDA | 2082.03 | 2199.0 | 2847.0 | 3892.0 | 4545.0 | 4150.0 | 6279.0 | 9439.0 | 11618.0 | |
| Earnings Per Share | AAPL | 6.45 | 9.22 | 8.31 | 9.21 | 11.91 | 11.89 | 3.28 | 5.61 | 6.11 |
| NVDA | 1.12 | 1.08 | 2.57 | 4.82 | 6.63 | 4.52 | 6.9 | 3.85 | 1.74 | |
| Gross Profit | AAPL | 70537.0 | 93626.0 | 84263.0 | 88186.0 | 101839.0 | 98392.0 | 104956.0 | 152836.0 | 170782.0 |
| NVDA | 2599.477 | 2811.0 | 4063.0 | 5822.0 | 7171.0 | 6768.0 | 10396.0 | 17475.0 | 15356.0 | |
| Inventories | AAPL | 2111.0 | 2349.0 | 2132.0 | 4855.0 | 3956.0 | 4106.0 | 4061.0 | 6580.0 | 4946.0 |
| NVDA | 482.893 | 418.0 | 794.0 | 796.0 | 1575.0 | 979.0 | 1826.0 | 2605.0 | 5159.0 | |
| Current Liabilities | AAPL | 63448.0 | 80610.0 | 79006.0 | 100814.0 | 116866.0 | 105718.0 | 105392.0 | 125481.0 | 153982.0 |
| NVDA | 896.03 | 2351.0 | 1788.0 | 1153.0 | 1329.0 | 1784.0 | 3925.0 | 4335.0 | 6563.0 | |
| Non-current Liabilities | AAPL | 56844.0 | 90514.0 | 114431.0 | 140458.0 | 141712.0 | 142310.0 | 153157.0 | 162431.0 | 148101.0 |
| NVDA | 1887.0 | 463.0 | 2260.0 | 2617.0 | 2621.0 | 3327.0 | 7973.0 | 13240.0 | 12518.0 | |
| Current Long Term Debt | AAPL | 0.0 | 2500.0 | 3500.0 | 6496.0 | 8784.0 | 10260.0 | 8773.0 | 9613.0 | 11128.0 |
| NVDA | 0.0 | 1413.0 | 796.0 | 15.0 | 0.0 | 0.0 | 999.0 | 0.0 | 1250.0 | |
| Non-current Long Term Debt | AAPL | 28987.0 | 53463.0 | 75427.0 | 97207.0 | 93735.0 | 91807.0 | 98667.0 | 109106.0 | 98959.0 |
| NVDA | 1384.342 | 0.0 | 1983.0 | 1985.0 | 1988.0 | 5964.0 | 5964.0 | 10946.0 | 9703.0 | |
| Net Cash Flow from Operating Activities | AAPL | 59713.0 | 81266.0 | 65824.0 | 63598.0 | 77434.0 | 69391.0 | 80674.0 | 104038.0 | 122151.0 |
| NVDA | 905.656 | 1175.0 | 1672.0 | 3502.0 | 3743.0 | 4761.0 | 5822.0 | 9108.0 | 5641.0 | |
| Operating Profit | AAPL | 52503.0 | 71230.0 | 60024.0 | 61344.0 | 70898.0 | 63930.0 | 66288.0 | 108949.0 | 119437.0 |
| NVDA | 758.989 | 747.0 | 1934.0 | 3210.0 | 3804.0 | 2846.0 | 4532.0 | 10041.0 | 4224.0 | |
| Other Current Assets | AAPL | 9806.0 | 9539.0 | 8283.0 | 13936.0 | 12087.0 | 12352.0 | 11264.0 | 14111.0 | 21223.0 |
| NVDA | 70.174 | 93.0 | 118.0 | 86.0 | 136.0 | 157.0 | 239.0 | 366.0 | 791.0 | |
| Other Current Liabilities | AAPL | 0.0 | 0.0 | 0.0 | 0.0 | 32687.0 | 37720.0 | 42684.0 | 47493.0 | 60845.0 |
| NVDA | 602.807 | 642.0 | 507.0 | 542.0 | 818.0 | 1097.0 | 1725.0 | 2552.0 | 4120.0 | |
| Total Net Sales | AAPL | 182795.0 | 233715.0 | 215639.0 | 229234.0 | 265595.0 | 260174.0 | 274515.0 | 365817.0 | 394328.0 |
| NVDA | 4682.0 | 5010.0 | 6910.0 | 9714.0 | 11716.0 | 10918.0 | 16675.0 | 26914.0 | 26974.0 |
def create_graph(metrics, table, ylabel = 'Percentage %'):
fig, axes = plt.subplots(nrows=len(metrics), ncols=1, figsize=(10, 5*len(metrics)), sharex=True)
for i, metric in enumerate(metrics):
ax = axes[i] if len(metrics) > 1 else axes
for stock_id in stock_ids:
ax.plot(table.columns, table.loc[metric, stock_id], label = stock_id)
ax.set_title(metric)
ax.set_xlabel('Years')
ax.set_ylabel(ylabel)
ax.legend()
Net assets = Non-current assets + current assets - current liabilities
Return on net assets (RONA): RONA measures ROI by expressing the operating profit before interest charges and taxation as a percentage of the company's net assets. RONA is a good measure of how effective the firm manages its net assets. It is not affected by the way in which the business is funded, which makes it a good ratio for comparison with prior years and with other businesses.
# Missing - Profit attributable to equity shareholders, closing share price, earnings per share, dividend per share, operating cash flow per share
fact = ['Sales Revenue', 'Operating Profit', 'Net Assets', 'Return on Net Assets %', 'Current Liabilities Ratio %']
stock_ids = ['AAPL', 'NVDA']
years = data.columns
multi_index = pd.MultiIndex.from_product([fact, stock_ids], names=['fact', 'stock_id'])
summary_table = pd.DataFrame(index=multi_index, columns=years)
for x in years:
for stock_id in stock_ids:
summary_table.loc[('Sales Revenue', stock_id), x] = data.loc[('Total Net Sales', stock_id), x]
summary_table.loc[('Operating Profit', stock_id), x] = data.loc[('Operating Profit', stock_id), x]
summary_table.loc[('Net Assets', stock_id), x] = data.loc[('Non-current Assets', stock_id), x] + data.loc[('Current Assets', stock_id), x] - data.loc[('Current Liabilities', stock_id), x]
summary_table.loc[('Return on Net Assets %', stock_id), x] = (summary_table.loc[('Operating Profit', stock_id), x] / summary_table.loc[('Net Assets', stock_id), x]) * 100
summary_table.loc[('Current Liabilities Ratio %', stock_id), x] = (data.loc[('Current Liabilities', stock_id), x] / summary_table.loc[('Net Assets', stock_id), x]) * 100
summary_table
| year | 2014 | 2015 | 2016 | 2017 | 2018 | 2019 | 2020 | 2021 | 2022 | |
|---|---|---|---|---|---|---|---|---|---|---|
| fact | stock_id | |||||||||
| Sales Revenue | AAPL | 182795.0 | 233715.0 | 215639.0 | 229234.0 | 265595.0 | 260174.0 | 274515.0 | 365817.0 | 394328.0 |
| NVDA | 4682.0 | 5010.0 | 6910.0 | 9714.0 | 11716.0 | 10918.0 | 16675.0 | 26914.0 | 26974.0 | |
| Operating Profit | AAPL | 52503.0 | 71230.0 | 60024.0 | 61344.0 | 70898.0 | 63930.0 | 66288.0 | 108949.0 | 119437.0 |
| NVDA | 758.989 | 747.0 | 1934.0 | 3210.0 | 3804.0 | 2846.0 | 4532.0 | 10041.0 | 4224.0 | |
| Net Assets | AAPL | 168391.0 | 209869.0 | 242680.0 | 274505.0 | 248859.0 | 232798.0 | 218496.0 | 225521.0 | 198773.0 |
| NVDA | 6305.267 | 5019.0 | 8053.0 | 10088.0 | 11963.0 | 15531.0 | 24866.0 | 39852.0 | 34619.0 | |
| Return on Net Assets % | AAPL | 31.17922 | 33.94022 | 24.733806 | 22.347134 | 28.489225 | 27.461576 | 30.338313 | 48.309913 | 60.087135 |
| NVDA | 12.037381 | 14.883443 | 24.015895 | 31.819984 | 31.798044 | 18.324641 | 18.22569 | 25.195724 | 12.201392 | |
| Current Liabilities Ratio % | AAPL | 37.678973 | 38.409675 | 32.555629 | 36.725743 | 46.960729 | 45.411902 | 48.235208 | 55.640495 | 77.466255 |
| NVDA | 14.210818 | 46.842 | 22.202906 | 11.429421 | 11.109254 | 11.486704 | 15.784605 | 10.877748 | 18.957798 |
fig, ax1 = plt.subplots(figsize=(10, 6))
metrics = ['Return on Net Assets %', 'Current Liabilities Ratio %']
ax1.plot(summary_table.columns, summary_table.loc[metrics[0], 'AAPL'], label=f'AAPL - {metrics[0]}', linestyle='-', marker='o', color='#3CB043')
ax1.plot(summary_table.columns, summary_table.loc[metrics[0], 'NVDA'], label=f'NVDA - {metrics[0]}', linestyle='-', marker='o', color='#F04A00')
ax1.set_xlabel('Years')
ax1.set_ylabel(metrics[0], color='blue')
ax1.tick_params(axis='y', labelcolor='blue')
ax1.legend(loc='upper left')
ax2 = ax1.twinx()
ax2.plot(summary_table.columns, summary_table.loc[metrics[1], 'AAPL'], label=f'AAPL - {metrics[1]}', linestyle='--', marker='x', color='#5DBB63')
ax2.plot(summary_table.columns, summary_table.loc[metrics[1], 'NVDA'], label=f'NVDA - {metrics[1]}', linestyle='--', marker='x', color='#F28500')
ax2.set_ylabel(metrics[1], color='red')
ax2.tick_params(axis='y', labelcolor='red')
ax2.legend(loc='upper right')
plt.title('Return on Net Assets % and Current Liabilities Ratio % over Years')
plt.grid(True)
plt.show()
Observations
Analysis
Conversely, in the case of NVDA, the inverse correlation between a high 'Return on Net Assets' and a low 'Current Liabilities Ratio' indicates efficient utilization of assets to generate strong returns while maintaining a relatively lower reliance on short-term liabilities. This could indicate a conservative approach in managing liabilities while maximizing returns on assets.
NVDA wins
# Missing - Profit attributable to equity shareholders
fact = ['Sales Revenue', 'Gross Profit', 'Operating Profit', 'Gross Profit %', 'Operating Profit %', 'Sales Revenue % change', 'Gross Profit % change', 'Operating Profit % change', 'Gross vs Operating Ratio']
multi_index = pd.MultiIndex.from_product([fact, stock_ids], names=['fact', 'stock_id'])
income_statement_table = pd.DataFrame(index=multi_index, columns=years)
for x in years:
for stock_id in stock_ids:
income_statement_table.loc[('Sales Revenue', stock_id), x] = data.loc[('Total Net Sales', stock_id), x]
income_statement_table.loc[('Gross Profit', stock_id), x] = data.loc[('Gross Profit', stock_id), x]
income_statement_table.loc[('Operating Profit', stock_id), x] = data.loc[('Operating Profit', stock_id), x]
income_statement_table.loc[('Gross Profit %', stock_id), x] = (income_statement_table.loc[('Gross Profit', stock_id), x] / income_statement_table.loc[('Sales Revenue', stock_id), x]) * 100
income_statement_table.loc[('Operating Profit %', stock_id), x] = (income_statement_table.loc[('Operating Profit', stock_id), x] / income_statement_table.loc[('Sales Revenue', stock_id), x]) * 100
income_statement_table.loc[('Gross vs Operating Ratio', stock_id), x] = income_statement_table.loc[('Gross Profit', stock_id), x] / income_statement_table.loc[('Operating Profit', stock_id), x]
for stock_id in stock_ids:
income_statement_table.loc[('Sales Revenue % change', stock_id)] = income_statement_table.loc[('Sales Revenue', stock_id)].pct_change(periods = 1)
income_statement_table.loc[('Gross Profit % change', stock_id)] = income_statement_table.loc[('Gross Profit', stock_id)].pct_change(periods = 1)
income_statement_table.loc[('Operating Profit % change', stock_id)] = income_statement_table.loc[('Operating Profit', stock_id)].pct_change(periods = 1)
income_statement_table
| year | 2014 | 2015 | 2016 | 2017 | 2018 | 2019 | 2020 | 2021 | 2022 | |
|---|---|---|---|---|---|---|---|---|---|---|
| fact | stock_id | |||||||||
| Sales Revenue | AAPL | 182795.0 | 233715.0 | 215639.0 | 229234.0 | 265595.0 | 260174.0 | 274515.0 | 365817.0 | 394328.0 |
| NVDA | 4682.0 | 5010.0 | 6910.0 | 9714.0 | 11716.0 | 10918.0 | 16675.0 | 26914.0 | 26974.0 | |
| Gross Profit | AAPL | 70537.0 | 93626.0 | 84263.0 | 88186.0 | 101839.0 | 98392.0 | 104956.0 | 152836.0 | 170782.0 |
| NVDA | 2599.477 | 2811.0 | 4063.0 | 5822.0 | 7171.0 | 6768.0 | 10396.0 | 17475.0 | 15356.0 | |
| Operating Profit | AAPL | 52503.0 | 71230.0 | 60024.0 | 61344.0 | 70898.0 | 63930.0 | 66288.0 | 108949.0 | 119437.0 |
| NVDA | 758.989 | 747.0 | 1934.0 | 3210.0 | 3804.0 | 2846.0 | 4532.0 | 10041.0 | 4224.0 | |
| Gross Profit % | AAPL | 38.588036 | 40.059902 | 39.075956 | 38.46986 | 38.343719 | 37.817768 | 38.233248 | 41.77936 | 43.309631 |
| NVDA | 55.520654 | 56.107784 | 58.798842 | 59.934116 | 61.206897 | 61.989375 | 62.344828 | 64.929033 | 56.928894 | |
| Operating Profit % | AAPL | 28.722339 | 30.477291 | 27.83541 | 26.760428 | 26.694027 | 24.572017 | 24.147314 | 29.782378 | 30.288744 |
| NVDA | 16.210786 | 14.91018 | 27.988423 | 33.04509 | 32.468419 | 26.067045 | 27.178411 | 37.307721 | 15.659524 | |
| Sales Revenue % change | AAPL | NaN | 0.278563 | -0.077342 | 0.063045 | 0.15862 | -0.020411 | 0.055121 | 0.332594 | 0.077938 |
| NVDA | NaN | 0.070056 | 0.379242 | 0.405789 | 0.206094 | -0.068112 | 0.527294 | 0.614033 | 0.002229 | |
| Gross Profit % change | AAPL | NaN | 0.327332 | -0.100004 | 0.046557 | 0.15482 | -0.033848 | 0.066713 | 0.456191 | 0.11742 |
| NVDA | NaN | 0.081371 | 0.445393 | 0.432931 | 0.231707 | -0.056199 | 0.536052 | 0.680935 | -0.121259 | |
| Operating Profit % change | AAPL | NaN | 0.356684 | -0.157321 | 0.021991 | 0.155745 | -0.098282 | 0.036884 | 0.64357 | 0.096265 |
| NVDA | NaN | -0.015796 | 1.589023 | 0.659772 | 0.185047 | -0.25184 | 0.59241 | 1.215578 | -0.579325 | |
| Gross vs Operating Ratio | AAPL | 1.343485 | 1.314418 | 1.403822 | 1.437565 | 1.436416 | 1.539058 | 1.583333 | 1.402822 | 1.429892 |
| NVDA | 3.424921 | 3.763052 | 2.100827 | 1.813707 | 1.885121 | 2.378074 | 2.29391 | 1.740365 | 3.635417 |
fig, ax1 = plt.subplots(figsize=(10, 6))
metrics = ['Gross Profit %', 'Operating Profit %']
ax1.plot(income_statement_table.columns, income_statement_table.loc[metrics[0], 'AAPL'], label=f'AAPL - {metrics[0]}', linestyle='-', marker='o', color='#3CB043')
ax1.plot(income_statement_table.columns, income_statement_table.loc[metrics[0], 'NVDA'], label=f'NVDA - {metrics[0]}', linestyle='-', marker='o', color='#F04A00')
ax1.plot(income_statement_table.columns, income_statement_table.loc[metrics[1], 'AAPL'], label=f'AAPL - {metrics[1]}', linestyle='--', marker='x', color='#5DBB63')
ax1.plot(income_statement_table.columns, income_statement_table.loc[metrics[1], 'NVDA'], label=f'NVDA - {metrics[1]}', linestyle='--', marker='x', color='#F28500')
ax1.set_xlabel('Years')
ax1.set_ylabel(metrics[0], color='blue')
ax1.tick_params(axis='y', labelcolor='blue')
ax1.legend(loc='upper left')
plt.title('Gross Profit % and Operating Profit %')
plt.grid(True)
plt.show()
fig, ax1 = plt.subplots(figsize=(10, 6))
metrics = ['Gross vs Operating Ratio']
ax1.plot(income_statement_table.columns, income_statement_table.loc[metrics[0], 'AAPL'], label=f'AAPL - {metrics[0]}', linestyle='-', marker='o', color='#3CB043')
ax1.plot(income_statement_table.columns, income_statement_table.loc[metrics[0], 'NVDA'], label=f'NVDA - {metrics[0]}', linestyle='-', marker='o', color='#F04A00')
ax1.set_xlabel('Years')
ax1.tick_params(axis='y', labelcolor='blue')
ax1.legend(loc='upper left')
plt.title('Gross vs Operating Ratio')
plt.grid(True)
plt.show()
Observation
Analysis
A substantial gap between gross profit and operating profit often indicates that a company is incurring significant operating expenses, such as administrative, selling, or research and development costs, after accounting for the cost of goods sold (COGS). This gap reflects the impact of these expenses on the overall profitability of the company, highlighting the difference between the revenue generated from sales and the earnings after considering both COGS and operational expenses.
(Further Analysis: Need to find out what are the operating expenses of NVDA and AAPL)
To Note for NVDA:
# Net Working Capital = Current Assets - Current Liabilities
# Net Assets = Non-current Assets + Current Assets - Current Liabilities
fact = ['Net Assets', 'Non-current Assets', 'Current Assets', 'Current Liabilities excluding loans', 'Net Working Capital', 'Debt', 'Non-current Assets %', 'Current Assets %', 'Current Liabilities %', 'Net Working Capital %', 'Debt %', 'Net Working Capital vs Debt Ratio']
multi_index = pd.MultiIndex.from_product([fact, stock_ids], names=['fact', 'stock_id'])
balance_sheet_table = pd.DataFrame(index=multi_index, columns=years)
for x in years:
for stock_id in stock_ids:
balance_sheet_table.loc[('Net Assets', stock_id), x] = summary_table.loc[('Net Assets', stock_id), x]
balance_sheet_table.loc[('Non-current Assets', stock_id), x] = data.loc[('Non-current Assets', stock_id), x]
balance_sheet_table.loc[('Current Assets', stock_id), x] = data.loc[('Current Assets', stock_id), x]
balance_sheet_table.loc[('Current Liabilities excluding loans', stock_id), x] = data.loc[('Trade Creditors', stock_id), x] + data.loc[('Other Current Liabilities', stock_id), x]
balance_sheet_table.loc[('Net Working Capital', stock_id), x] = data.loc[('Current Assets', stock_id), x] - (data.loc[('Trade Creditors', stock_id), x] + data.loc[('Other Current Liabilities', stock_id), x])
balance_sheet_table.loc[('Debt', stock_id), x] = data.loc[('Current Long Term Debt', stock_id), x] + data.loc[('Non-current Long Term Debt', stock_id), x]
balance_sheet_table.loc[('Non-current Assets %', stock_id), x] = (data.loc[('Non-current Assets', stock_id), x] / balance_sheet_table.loc[('Net Assets', stock_id), x]) * 100
balance_sheet_table.loc[('Current Assets %', stock_id), x] = (data.loc[('Current Assets', stock_id), x] / balance_sheet_table.loc[('Net Assets', stock_id), x]) * 100
balance_sheet_table.loc[('Current Liabilities %', stock_id), x] = (data.loc[('Current Liabilities', stock_id), x] / balance_sheet_table.loc[('Net Assets', stock_id), x]) * 100
balance_sheet_table.loc[('Net Working Capital %', stock_id), x] = (balance_sheet_table.loc[('Net Working Capital', stock_id), x] / balance_sheet_table.loc[('Net Assets', stock_id), x]) * 100
balance_sheet_table.loc[('Debt %', stock_id), x] = (balance_sheet_table.loc[('Debt', stock_id), x] / balance_sheet_table.loc[('Net Assets', stock_id), x]) * 100
balance_sheet_table.loc[('Net Working Capital vs Debt Ratio', stock_id), x] = (balance_sheet_table.loc[('Net Working Capital', stock_id), x] / balance_sheet_table.loc[('Debt', stock_id), x])
balance_sheet_table
| year | 2014 | 2015 | 2016 | 2017 | 2018 | 2019 | 2020 | 2021 | 2022 | |
|---|---|---|---|---|---|---|---|---|---|---|
| fact | stock_id | |||||||||
| Net Assets | AAPL | 168391.0 | 209869.0 | 242680.0 | 274505.0 | 248859.0 | 232798.0 | 218496.0 | 225521.0 | 198773.0 |
| NVDA | 6305.267 | 5019.0 | 8053.0 | 10088.0 | 11963.0 | 15531.0 | 24866.0 | 39852.0 | 34619.0 | |
| Non-current Assets | AAPL | 163308.0 | 201101.0 | 214817.0 | 246674.0 | 234386.0 | 175697.0 | 180175.0 | 216166.0 | 217350.0 |
| NVDA | 1488.0 | 1317.0 | 1305.0 | 1986.0 | 2735.0 | 3625.0 | 12736.0 | 15358.0 | 18109.0 | |
| Current Assets | AAPL | 68531.0 | 89378.0 | 106869.0 | 128645.0 | 131339.0 | 162819.0 | 143713.0 | 134836.0 | 135405.0 |
| NVDA | 5713.297 | 6053.0 | 8536.0 | 9255.0 | 10557.0 | 13690.0 | 16055.0 | 28829.0 | 23073.0 | |
| Current Liabilities excluding loans | AAPL | 30196.0 | 35490.0 | 37294.0 | 49049.0 | 88575.0 | 83956.0 | 84980.0 | 102256.0 | 124960.0 |
| NVDA | 896.03 | 938.0 | 992.0 | 1138.0 | 1329.0 | 1784.0 | 2926.0 | 4335.0 | 5313.0 | |
| Net Working Capital | AAPL | 38335.0 | 53888.0 | 69575.0 | 79596.0 | 42764.0 | 78863.0 | 58733.0 | 32580.0 | 10445.0 |
| NVDA | 4817.267 | 5115.0 | 7544.0 | 8117.0 | 9228.0 | 11906.0 | 13129.0 | 24494.0 | 17760.0 | |
| Debt | AAPL | 28987.0 | 55963.0 | 78927.0 | 103703.0 | 102519.0 | 102067.0 | 107440.0 | 118719.0 | 110087.0 |
| NVDA | 1384.342 | 1413.0 | 2779.0 | 2000.0 | 1988.0 | 5964.0 | 6963.0 | 10946.0 | 10953.0 | |
| Non-current Assets % | AAPL | 96.98143 | 95.822156 | 88.518625 | 89.861387 | 94.184257 | 75.471868 | 82.461464 | 95.851828 | 109.345837 |
| NVDA | 23.599318 | 26.240287 | 16.205141 | 19.686757 | 22.862158 | 23.340416 | 51.218531 | 38.537589 | 52.309425 | |
| Current Assets % | AAPL | 40.697543 | 42.587519 | 44.037003 | 46.864356 | 52.776472 | 69.940034 | 65.773744 | 59.788667 | 68.120419 |
| NVDA | 90.6115 | 120.601713 | 105.997765 | 91.742665 | 88.247095 | 88.146288 | 64.566074 | 72.340159 | 66.648372 | |
| Current Liabilities % | AAPL | 37.678973 | 38.409675 | 32.555629 | 36.725743 | 46.960729 | 45.411902 | 48.235208 | 55.640495 | 77.466255 |
| NVDA | 14.210818 | 46.842 | 22.202906 | 11.429421 | 11.109254 | 11.486704 | 15.784605 | 10.877748 | 18.957798 | |
| Net Working Capital % | AAPL | 22.765468 | 25.67697 | 28.669441 | 28.996193 | 17.184028 | 33.87615 | 26.880584 | 14.446548 | 5.254738 |
| NVDA | 76.400682 | 101.912732 | 93.679374 | 80.461935 | 77.137842 | 76.659584 | 52.799003 | 61.462411 | 51.301309 | |
| Debt % | AAPL | 17.214103 | 26.665682 | 32.523076 | 37.778183 | 41.195617 | 43.84359 | 49.172525 | 52.642104 | 55.383276 |
| NVDA | 21.955327 | 28.153019 | 34.508879 | 19.825535 | 16.617905 | 38.400618 | 28.002091 | 27.466627 | 31.638696 | |
| Net Working Capital vs Debt Ratio | AAPL | 1.322489 | 0.962922 | 0.881511 | 0.767538 | 0.417132 | 0.772659 | 0.546659 | 0.27443 | 0.09488 |
| NVDA | 3.479824 | 3.619958 | 2.714646 | 4.0585 | 4.641851 | 1.996311 | 1.885538 | 2.237712 | 1.621474 |
fig, ax1 = plt.subplots(figsize=(10, 6))
metrics = ['Net Working Capital %', 'Debt %']
ax1.plot(balance_sheet_table.columns, balance_sheet_table.loc[metrics[0], 'AAPL'], label=f'AAPL - {metrics[0]}', linestyle='-', marker='o', color='#3CB043')
ax1.plot(balance_sheet_table.columns, balance_sheet_table.loc[metrics[0], 'NVDA'], label=f'NVDA - {metrics[0]}', linestyle='-', marker='o', color='#F04A00')
ax1.plot(balance_sheet_table.columns, balance_sheet_table.loc[metrics[1], 'AAPL'], label=f'AAPL - {metrics[1]}', linestyle='--', marker='x', color='#5DBB63')
ax1.plot(balance_sheet_table.columns, balance_sheet_table.loc[metrics[1], 'NVDA'], label=f'NVDA - {metrics[1]}', linestyle='--', marker='x', color='#F28500')
ax1.set_xlabel('Years')
ax1.tick_params(axis='y', labelcolor='blue')
ax1.legend(loc='upper left')
plt.title('Net Working Capital % and Debt %')
plt.grid(True)
plt.show()
fig, ax1 = plt.subplots(figsize=(10, 6))
metrics = ['Net Working Capital vs Debt Ratio']
# Plot Return on Net Assets %
ax1.plot(balance_sheet_table.columns, balance_sheet_table.loc[metrics[0], 'AAPL'], label=f'AAPL - {metrics[0]}', linestyle='-', marker='o', color='#3CB043')
ax1.plot(balance_sheet_table.columns, balance_sheet_table.loc[metrics[0], 'NVDA'], label=f'NVDA - {metrics[0]}', linestyle='-', marker='o', color='#F04A00')
ax1.set_xlabel('Years')
ax1.set_ylabel(metrics[0], color='blue')
ax1.tick_params(axis='y', labelcolor='blue')
ax1.legend(loc='upper left')
plt.title('Net Working Capital vs Debt Ratio')
plt.grid(True)
plt.show()
Observations
Analysis
fact = ['Return on Net Assets %', 'Profit Margin %', 'Net Asset Turnover']
multi_index = pd.MultiIndex.from_product([fact, stock_ids], names=['fact', 'stock_id'])
profitability_ratios_table = pd.DataFrame(index=multi_index, columns=years)
for x in years:
for stock_id in stock_ids:
profitability_ratios_table.loc[('Return on Net Assets %', stock_id), x] = summary_table.loc[('Return on Net Assets %', stock_id), x]
profitability_ratios_table.loc[('Profit Margin %', stock_id), x] = (summary_table.loc[('Operating Profit', stock_id), x] / summary_table.loc[('Sales Revenue', stock_id), x]) * 100
profitability_ratios_table.loc[('Net Asset Turnover', stock_id), x] = summary_table.loc[('Sales Revenue', stock_id), x] / summary_table.loc[('Net Assets', stock_id), x]
profitability_ratios_table
| year | 2014 | 2015 | 2016 | 2017 | 2018 | 2019 | 2020 | 2021 | 2022 | |
|---|---|---|---|---|---|---|---|---|---|---|
| fact | stock_id | |||||||||
| Return on Net Assets % | AAPL | 31.17922 | 33.94022 | 24.733806 | 22.347134 | 28.489225 | 27.461576 | 30.338313 | 48.309913 | 60.087135 |
| NVDA | 12.037381 | 14.883443 | 24.015895 | 31.819984 | 31.798044 | 18.324641 | 18.22569 | 25.195724 | 12.201392 | |
| Profit Margin % | AAPL | 28.722339 | 30.477291 | 27.83541 | 26.760428 | 26.694027 | 24.572017 | 24.147314 | 29.782378 | 30.288744 |
| NVDA | 16.210786 | 14.91018 | 27.988423 | 33.04509 | 32.468419 | 26.067045 | 27.178411 | 37.307721 | 15.659524 | |
| Net Asset Turnover | AAPL | 1.085539 | 1.113623 | 0.888573 | 0.835081 | 1.067251 | 1.117596 | 1.256385 | 1.622097 | 1.983811 |
| NVDA | 0.742554 | 0.998207 | 0.858065 | 0.962926 | 0.979353 | 0.702981 | 0.670594 | 0.675349 | 0.779168 |
fig, ax1 = plt.subplots(figsize=(10, 6))
metrics = ['Return on Net Assets %']
ax1.plot(profitability_ratios_table.columns, profitability_ratios_table.loc[metrics[0], 'AAPL'], label=f'AAPL - {metrics[0]}', linestyle='-', marker='o', color='#3CB043')
ax1.plot(profitability_ratios_table.columns, profitability_ratios_table.loc[metrics[0], 'NVDA'], label=f'NVDA - {metrics[0]}', linestyle='-', marker='o', color='#F04A00')
ax1.set_xlabel('Years')
ax1.tick_params(axis='y', labelcolor='blue')
ax1.legend(loc='upper left')
plt.title('Return on Net Assets')
plt.grid(True)
plt.show()
Observations
Analysis
fig, ax1 = plt.subplots(figsize=(10, 6))
metrics = ['Profit Margin %']
ax1.plot(profitability_ratios_table.columns, profitability_ratios_table.loc[metrics[0], 'AAPL'], label=f'AAPL - {metrics[0]}', linestyle='-', marker='o', color='#3CB043')
ax1.plot(profitability_ratios_table.columns, profitability_ratios_table.loc[metrics[0], 'NVDA'], label=f'NVDA - {metrics[0]}', linestyle='-', marker='o', color='#F04A00')
ax1.set_xlabel('Years')
ax1.tick_params(axis='y', labelcolor='blue')
ax1.legend(loc='upper left')
plt.title('Profit Margin %')
plt.grid(True)
plt.show()
Observation:
Analysis:
fig, ax1 = plt.subplots(figsize=(10, 6))
metrics = ['Net Asset Turnover']
ax1.plot(profitability_ratios_table.columns, profitability_ratios_table.loc[metrics[0], 'AAPL'], label=f'AAPL - {metrics[0]}', linestyle='-', marker='o', color='#3CB043')
ax1.plot(profitability_ratios_table.columns, profitability_ratios_table.loc[metrics[0], 'NVDA'], label=f'NVDA - {metrics[0]}', linestyle='-', marker='o', color='#F04A00')
ax1.set_xlabel('Years')
ax1.tick_params(axis='y', labelcolor='blue')
ax1.legend(loc='upper left')
plt.title('Net Asset Turnover')
plt.grid(True)
plt.show()
Observations
Analysis
fact = ['Current ratio', 'Acid test ratio']
multi_index = pd.MultiIndex.from_product([fact, stock_ids], names=['fact', 'stock_id'])
liquidity_ratios_table = pd.DataFrame(index=multi_index, columns=years)
for x in years:
for stock_id in stock_ids:
liquidity_ratios_table.loc[('Current ratio', stock_id), x] = data.loc[('Current Assets', stock_id), x] / data.loc[('Current Liabilities', stock_id), x]
liquidity_ratios_table.loc[('Acid test ratio', stock_id), x] = (data.loc[('Trade Debtors', stock_id), x] + data.loc[('Cash and Cash Equivalents', stock_id), x]) / data.loc[('Current Liabilities', stock_id), x]
liquidity_ratios_table
| year | 2014 | 2015 | 2016 | 2017 | 2018 | 2019 | 2020 | 2021 | 2022 | |
|---|---|---|---|---|---|---|---|---|---|---|
| fact | stock_id | |||||||||
| Current ratio | AAPL | 1.080113 | 1.108771 | 1.352669 | 1.276063 | 1.123843 | 1.540126 | 1.363604 | 1.074553 | 0.879356 |
| NVDA | 6.376234 | 2.574649 | 4.774049 | 8.026886 | 7.943567 | 7.673767 | 4.090446 | 6.650288 | 3.515618 | |
| Acid test ratio | AAPL | 0.49338 | 0.471021 | 0.458674 | 0.378549 | 0.420131 | 0.678882 | 0.513663 | 0.487867 | 0.336598 |
| NVDA | 1.082878 | 0.468311 | 1.449664 | 4.568083 | 1.659895 | 7.036435 | 0.83465 | 1.531719 | 1.099497 |
fig, ax1 = plt.subplots(figsize=(10, 6))
metrics = ['Current ratio', 'Acid test ratio']
ax1.plot(liquidity_ratios_table.columns, liquidity_ratios_table.loc[metrics[0], 'AAPL'], label=f'AAPL - {metrics[0]}', linestyle='-', marker='o', color='#3CB043')
ax1.plot(liquidity_ratios_table.columns, liquidity_ratios_table.loc[metrics[0], 'NVDA'], label=f'NVDA - {metrics[0]}', linestyle='-', marker='o', color='#F04A00')
ax1.set_xlabel('Years')
ax1.set_ylabel(metrics[0], color='blue')
ax1.tick_params(axis='y', labelcolor='blue')
ax1.legend(loc='upper left')
ax2 = ax1.twinx()
ax2.plot(liquidity_ratios_table.columns, liquidity_ratios_table.loc[metrics[1], 'AAPL'], label=f'AAPL - {metrics[1]}', linestyle='--', marker='x', color='#5DBB63')
ax2.plot(liquidity_ratios_table.columns, liquidity_ratios_table.loc[metrics[1], 'NVDA'], label=f'NVDA - {metrics[1]}', linestyle='--', marker='x', color='#F28500')
ax2.set_ylabel(metrics[1], color='red')
ax2.tick_params(axis='y', labelcolor='red')
ax2.legend(loc='upper right')
plt.title('Current ratio and Acid test ratio over Years')
plt.grid(True)
plt.show()
fact = ['Operating Cash Flow ratio', 'Cash Flow Margin ratio', 'Cash Flow to Debt ratio']
multi_index = pd.MultiIndex.from_product([fact, stock_ids], names=['fact', 'stock_id'])
cash_flow_ratio_table = pd.DataFrame(index=multi_index, columns=years)
for x in years:
for stock_id in stock_ids:
cash_flow_ratio_table.loc[('Operating Cash Flow ratio', stock_id), x] = data.loc[('Net Cash Flow from Operating Activities', stock_id), x] / data.loc[('Current Liabilities', stock_id), x]
cash_flow_ratio_table.loc[('Cash Flow Margin ratio', stock_id), x] = (data.loc[('Net Cash Flow from Operating Activities', stock_id), x] / summary_table.loc[('Sales Revenue', stock_id), x]) * 100
cash_flow_ratio_table.loc[('Cash Flow to Debt ratio', stock_id), x] = (data.loc[('Net Cash Flow from Operating Activities', stock_id), x] / (data.loc[('Current Liabilities', stock_id), x] + data.loc[('Non-current Liabilities', stock_id), x])) * 100
cash_flow_ratio_table
| year | 2014 | 2015 | 2016 | 2017 | 2018 | 2019 | 2020 | 2021 | 2022 | |
|---|---|---|---|---|---|---|---|---|---|---|
| fact | stock_id | |||||||||
| Operating Cash Flow ratio | AAPL | 0.941133 | 1.008138 | 0.833152 | 0.630845 | 0.662588 | 0.656378 | 0.765466 | 0.829114 | 0.793281 |
| NVDA | 1.010743 | 0.499787 | 0.935123 | 3.037294 | 2.816403 | 2.668722 | 1.483312 | 2.101038 | 0.859515 | |
| Cash Flow Margin ratio | AAPL | 32.666648 | 34.77141 | 30.525091 | 27.743703 | 29.154916 | 26.670997 | 29.387829 | 28.439903 | 30.977004 |
| NVDA | 19.343358 | 23.453094 | 24.196816 | 36.05106 | 31.947764 | 43.606888 | 34.914543 | 33.841124 | 20.912731 | |
| Cash Flow to Debt ratio | AAPL | 49.640043 | 47.48954 | 34.02865 | 26.359462 | 29.94609 | 27.977083 | 31.202596 | 36.135347 | 40.436238 |
| NVDA | 32.542085 | 41.755508 | 41.304348 | 92.891247 | 94.759494 | 93.152025 | 48.932594 | 51.823613 | 29.56344 |
fig, ax1 = plt.subplots(figsize=(10, 6))
metrics = ['Operating Cash Flow ratio']
ax1.plot(cash_flow_ratio_table.columns, cash_flow_ratio_table.loc[metrics[0], 'AAPL'], label=f'AAPL - {metrics[0]}', linestyle='-', marker='o', color='#3CB043')
ax1.plot(cash_flow_ratio_table.columns, cash_flow_ratio_table.loc[metrics[0], 'NVDA'], label=f'NVDA - {metrics[0]}', linestyle='-', marker='o', color='#F04A00')
ax1.set_xlabel('Years')
ax1.set_ylabel(metrics[0], color='blue')
ax1.tick_params(axis='y', labelcolor='blue')
ax1.legend(loc='upper left')
plt.title('Operating Cash Flow ratio')
plt.grid(True)
plt.show()
fig, ax1 = plt.subplots(figsize=(10, 6))
metrics = ['Cash Flow Margin ratio']
ax1.plot(cash_flow_ratio_table.columns, cash_flow_ratio_table.loc[metrics[0], 'AAPL'], label=f'AAPL - {metrics[0]}', linestyle='-', marker='o', color='#3CB043')
ax1.plot(cash_flow_ratio_table.columns, cash_flow_ratio_table.loc[metrics[0], 'NVDA'], label=f'NVDA - {metrics[0]}', linestyle='-', marker='o', color='#F04A00')
ax2.set_ylabel(metrics[0], color='red')
ax2.tick_params(axis='y', labelcolor='red')
ax2.legend(loc='upper right')
plt.title('Cash Flow Margin ratio')
plt.grid(True)
plt.show()
fig, ax1 = plt.subplots(figsize=(10, 6))
metrics = ['Cash Flow to Debt ratio']
ax1.plot(cash_flow_ratio_table.columns, cash_flow_ratio_table.loc[metrics[0], 'AAPL'], label=f'AAPL - {metrics[0]}', linestyle='-', marker='o', color='#3CB043')
ax1.plot(cash_flow_ratio_table.columns, cash_flow_ratio_table.loc[metrics[0], 'NVDA'], label=f'NVDA - {metrics[0]}', linestyle='-', marker='o', color='#F04A00')
ax2.set_ylabel(metrics[0], color='red')
ax2.tick_params(axis='y', labelcolor='red')
ax2.legend(loc='upper right')
plt.title('Cash Flow to Debt ratio')
plt.grid(True)
plt.show()
Observations
Analysis
cnx.close()
NVDA typically outperforms AAPL across multiple metrics over the years. However, a decline in NVDA's metrics from 2021 to 2022 raises questions about the simultaneous increase in cumulative return during that period.
Despite NVDA's declining metrics, such as profitability, the sustained rise in cumulative return implies ongoing investor confidence in NVDA's long-term potential. This trend could be influenced by the growing significance of AI, further enhancing NVDA's attractiveness in the market.
Additionally, NVDA maintains a higher profit margin than AAPL but exhibits lower net asset turnover and return on net assets. This suggests NVDA generates substantial profits relative to sales revenue but might underutilize its assets for generating sales. While profitability per sales unit remains healthy, there's potential to improve asset utilization for driving higher sales relative to its asset base.
Here are potential areas for project enhancement in the future: